﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using log4net;

namespace MyLib {
	/// <summary>
	/// 
	/// </summary>
	public sealed class Session : IDisposable {
		/// <summary>
		/// Internal logger.
		/// </summary>
		private ILog m_logger = LogManager.GetLogger(typeof(Session));

		/// <summary>
		/// 
		/// </summary>
		private SessionFactory m_factory;

		/// <summary>
		/// 
		/// </summary>
		private DbConnection m_connection;

		/// <summary>
		/// 
		/// </summary>
		private DbTransaction m_transaction;

		/// <summary>
		/// 
		/// </summary>
		private bool m_isReadOnly;

		/// <summary>
		/// 
		/// </summary>
		private bool m_autoCloseConnection;

		/// <summary>
		/// Initialize session.
		/// </summary>
		/// <param name="factory"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		/// <param name="isReadOnly"></param>
		/// <param name="autoCloseConnection"></param>
		internal Session(SessionFactory factory, DbConnection connection, DbTransaction transaction, bool isReadOnly, bool autoCloseConnection) {
			System.Diagnostics.Debug.Assert(factory != null);
			System.Diagnostics.Debug.Assert(connection != null);
			System.Diagnostics.Debug.Assert(connection.State == ConnectionState.Open);

			m_factory = factory;
			m_connection = connection;
			m_transaction = transaction;
			m_isReadOnly = isReadOnly;
			m_autoCloseConnection = autoCloseConnection;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="reader"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		/// <returns></returns>
		private static DataTable CreateTableFromReader(IDataReader reader, int startRowIndex = 0, int maximumRows = int.MaxValue) {
			using (reader) {
				var dataTable = new DataTable();
				var fc = reader.FieldCount;

				dataTable.BeginInit();
				for (int i = 0; i < fc; ++i) {
					dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
				}
				dataTable.EndInit();

				// Skip startRowIndex rows.
				int s = 0;
				while (s++ < startRowIndex && reader.Read()) ;

				// Read maximumRow rows at most.
				dataTable.BeginLoadData();

				int r = 0;
				while (r++ < maximumRows && reader.Read()) {
					var row = dataTable.NewRow();
					for (int i = 0; i < fc; ++i) {
						row[i] = reader.GetValue(i);
					}
					dataTable.Rows.Add(row);
				}

				reader.Close();

				dataTable.EndLoadData();

				dataTable.AcceptChanges();

				return dataTable;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="reader"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		private static void FillTableFromReader(DataTable dataTable, IDataReader reader, int startRowIndex = 0, int maximumRows = int.MaxValue) {
			System.Diagnostics.Debug.Assert(dataTable != null);

			using (reader) {
				var fc = reader.FieldCount;

				// Skip startRowIndex rows.
				int s = 0;
				while (s++ < startRowIndex && reader.Read()) ;

				// Read maximumRow rows at most.
				int r = 0;
				while (r++ < maximumRows && reader.Read()) {
					var row = dataTable.NewRow();
					for (int i = 0; i < fc; ++i) {
						var v = reader.GetValue(i);
						var c = dataTable.Columns[reader.GetName(i)];
						if (c != null) {
							row[c] = v;
						}
					}
					dataTable.Rows.Add(row);
				}

				reader.Close();

				dataTable.AcceptChanges();
			}
		}

		/// <summary>
		/// Execute query.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DataTable ExecuteQuery(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			var r = CreateTableFromReader(command.ExecuteReader());
			GetOutputParameterValues(command, parameters);

			return r;
		}

		/// <summary>
		/// Execute query.
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		public void ExecuteQuery(System.Data.DataTable dataTable, string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			System.Diagnostics.Debug.Assert(dataTable != null);

			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			FillTableFromReader(dataTable, command.ExecuteReader());
			GetOutputParameterValues(command, parameters);
		}

		/// <summary>
		/// Execute paged query.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		/// <returns></returns>
		public System.Data.DataTable ExecutePagedQuery(string sql, System.Data.CommandType commandType, int startRowIndex, int maximumRows, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			var r = CreateTableFromReader(command.ExecuteReader(), startRowIndex, maximumRows);
			GetOutputParameterValues(command, parameters);

			return r;
		}

		/// <summary>
		/// Execute paged query.
		/// </summary>
		/// <param name="dataTable"></param>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <param name="startRowIndex"></param>
		/// <param name="maximumRows"></param>
		/// <returns></returns>
		public void ExecutePagedQuery(System.Data.DataTable dataTable, string sql, System.Data.CommandType commandType, int startRowIndex, int maximumRows, params DbParameter[] parameters) {
			System.Diagnostics.Debug.Assert(dataTable != null);

			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			FillTableFromReader(dataTable, command.ExecuteReader(), startRowIndex, maximumRows);
			GetOutputParameterValues(command, parameters);
		}

		/// <summary>
		/// Execute query and returns a list.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public IList<T> ExecuteList<T>(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			var list = new List<T>();
			using (var reader = command.ExecuteReader()) {
				while (reader.Read()) {
					list.Add((T)Convert.ChangeType(reader.GetValue(0), typeof(T)));
				}

				reader.Close();

				GetOutputParameterValues(command, parameters);
			}

			return list;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="command"></param>
		/// <param name="parameters"></param>
		private static void GetOutputParameterValues(DbCommand command, params DbParameter[] parameters) {
			for (int i = 0; i < parameters.Length; ++i) {
				var parameter = parameters[i];

				if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput) {
					parameter.Value = command.Parameters[i].Value;
				}
			}
		}

		/// <summary>
		/// Execute query and returns a scalar value.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public T ExecuteScalar<T>(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			var r = command.ExecuteScalar();
			GetOutputParameterValues(command, parameters);

			return (T)Convert.ChangeType(r, typeof(T));
		}

		/// <summary>
		/// Execute update.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public int ExecuteUpdate(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			var r = command.ExecuteNonQuery();
			GetOutputParameterValues(command, parameters);

			return r;
		}

		/// <summary>
		/// Execute reader.
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="commandType"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public DbDataReader ExecuteReader(string sql, System.Data.CommandType commandType, params DbParameter[] parameters) {
			var command = m_factory.CreateCommand(sql, commandType, parameters);
			command.Connection = m_connection;
			if (m_transaction != null) {
				command.Transaction = m_transaction;
			}

			return command.ExecuteReader();
		}

		/// <summary>
		/// Commit session.
		/// </summary>
		public void Commit() {
			if (!m_autoCloseConnection) {
				m_connection = null;
				m_transaction = null;
				return;
			}

			// Commit any transaction.
			if (m_transaction != null) {
				if (m_logger.IsDebugEnabled) {
					m_logger.DebugFormat("Commit a transaction.");
				}
				m_transaction.Commit();

				m_transaction = null;
			}

			// Close any connection.
			if (m_connection != null) {
				try {
					m_connection.Close();
				} catch {
				}

				m_connection = null;
			}
		}

		/// <summary>
		/// Close session.
		/// </summary>
		public void Close() {
			if (!m_autoCloseConnection) {
				m_connection = null;
				m_transaction = null;
				return;
			}

			// Rollback any transaction.
			if (m_transaction != null) {
				if (m_logger.IsDebugEnabled) {
					m_logger.DebugFormat("Rollback a transaction.");
				}
				m_transaction.Rollback();
				m_transaction = null;
			}

			// Close any connection.
			if (m_connection != null) {
				try {
					m_connection.Close();
				} catch {
				}

				m_connection = null;
			}
		}

		/// <summary>
		/// Release all resources.
		/// </summary>
		public void Dispose() {
			Close();
		} // end of Dispose.
	} // end of Session.
}
